package com.hz.ys_financial.dao;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;


import com.hz.framework.MyInterFace.SystemInterFace;
import com.hz.framework.dao.CommonDBDao;
import com.hz.framework.dao.CommonDao;
import com.hz.framework.util.DateUtil;
import com.hz.framework.util.SqlParameter;
import com.hz.ys_financial.bean.Takingsorpay;
import com.hz.ys_financial.dbsql.FinancialSql;
import com.hz.ys_stock.dbsql.StockSql;
import com.hz.ys_system.dao.QueryDao;

/**
 * *****************************************************************************
 *               Copyright(c) 2010 by 哈尔滨华泽数码科技有限公司.
 *                       All rights reserved.
 *******************************************************************************
 *     File Name         :  com.hz.ys_stock.dao.SupplierDao.java
 *     Description(说明)	:  采购单付款信息
 * -----------------------------------------------------------------------------
 *     No.        Date              Revised by           Description	  
 *     1		Jun 8, 2011				韩飞	              Created
 *****************************************************************************
 */
public class FinancialDao implements SystemInterFace{
	
	/**
	 * @author 韩飞
	 * @time Nov 25, 2011	
	 * @param request 
	 * @param tpflag   purchase 采购 或 order 订单  plan 订单预付款
	 * @return 
	 * 方法功能: 查询所有采购单付款信息
	 */
	public String SelFinancial(HttpServletRequest request,String tpflag){
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append(FinancialSql.SEL_FINANCIAL);
		sbSQL.append(" and tpflag = '"+tpflag+"'");
		sbSQL.append(" and flag = 'y'");
		
		sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("suppliername","tpperson") , "=") );
		
		sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("tpway") , "like") );
		
		//时间条件查询
		sbSQL.append(new QueryDao().QueryDateInfo(request,"tpdate","begin","end",true));
		
		return sbSQL.toString();
	}
	/**
	 * @author 韩飞
	 * @time Nov 25, 2011	
	 * @return 
	 * 方法功能: 查询所有没付款的采购单
	 */
	public List SelPurchaseList(){
		CommonDBDao dbo= new CommonDBDao();
		return dbo.DBjsonList(FinancialSql.SEL_PURCHASE);
	}
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能:查询所有人员
	 */
	public List SelPerson(){
		CommonDBDao dbo= new CommonDBDao();
		return dbo.DBjsonList(StockSql.SEL_BUYER);
	}
	/**
	 * @author 韩飞
	 * @time Nov 17, 2011	
	 * @param puser
	 * @return
	 * @throws Exception 
	 * 方法功能: 保存采购单付款信息
	 */
	public boolean DoSavePayMoney( Takingsorpay to ,String tpflag) throws Exception {
		boolean ins = false;
		CommonDao dbd= new CommonDao();
		if(to!=null){
			//保存或更新 下拉选名称表
			to.setTpflag(tpflag);
			to.setCreatetime(DateUtil.getDateTime());
			if(to.getRealmoney().equals(to.getTpmoney())){
				to.setFinalflag("已结算");
			}else{
//				还剩欠款
				to.setFinalflag("余付款");
			}
			ins = dbd.insertinfor(to);
			if(ins){
				ins = dbd.insorupd(FinancialSql.UPD_PURCHASE_INFO + " ("+to.getOrderId().substring(0, to.getOrderId().lastIndexOf(","))+") ");
			}
		}
		return ins;
	}
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @param request
	 * @return 
	 * 方法功能: 删除 采购单付款信息
	 */
	public boolean Del_PayMoney(HttpServletRequest request){
		String tpid = CommonDao.isnull(request.getParameter("tpid"));
		CommonDBDao dbo= new CommonDBDao();
		return dbo.executeSQL(FinancialSql.DEL_PAYMONEY, new SqlParameter(tpid));
	}
	
	
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @param request
	 * @return 
	 * 方法功能: 根据 sid 查询 对应的采购付款信息
	 */
	public void SelPayMoneyInfo(HttpServletRequest request,List list) throws Exception {
		String tpid = CommonDao.isnull(request.getParameter("tpid"));
		CommonDao dbd= new CommonDao();
		Map map = (Map)dbd.queryObjectByPS(FinancialSql.SEL_PAYMONEY_INFO, new SqlParameter(tpid),null);
		request.setAttribute("updinfo", map);
		
		String where = "";
		String orderId = (String) map.get("orderId");
		if(!"".equals(orderId) && orderId.contains(",")){
			String[] orderIds = orderId.split(",");
			where +=" where ";
			for(int i= 0;i<orderIds.length;i++){
				if(!"".equals(orderIds[i])){
					where +=" p.purchaseid ='"+orderIds[i]+"' or ";
				}
			}
			where = where.substring(0, where.lastIndexOf("or "));
		}
		List purchaselist = dbd.DBjsonList(FinancialSql.SEL_PAYMONEY_PURCHASE + where);
		if(list!=null){
			purchaselist.addAll(list);
		}
		request.setAttribute("purchaselist",purchaselist );
		
	}
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能: 修改采购付款信息
	 */
	public boolean UpdPayMoney( Takingsorpay to ,String tpflag){
		boolean ins = false;
		CommonDao dbo= new CommonDao();
		if(to!=null){
			//保存或更新 下拉选名称表
			to.setTpflag(tpflag);
			to.setCreatetime(DateUtil.getDateTime());
			if(to.getRealmoney().equals(to.getTpmoney())){
				to.setFinalflag("已结算");
			}else{
//				还剩欠款
				to.setFinalflag("余付款");
			}
			ins = dbo.updateinfor(to);
			if(ins){
				ins = dbo.insorupd(FinancialSql.UPD_PURCHASE_INFO + " ("+to.getOrderId().substring(0, to.getOrderId().lastIndexOf(","))+") ");
			}
		}
		return ins;
	}
	/*   订单结算方法      */
	/**
	 * @author 韩飞
	 * @time Nov 25, 2011	
	 * @return 
	 * 方法功能: 查询所有未收款的订单信息
	 */
	public List SelOrderList(){
		CommonDBDao dbo= new CommonDBDao();
		return dbo.DBjsonList(FinancialSql.SEL_ORDER_LIST);
	}
	/**
	 * @author 韩飞
	 * @time Nov 17, 2011	
	 * @param puser
	 * @return
	 * @throws Exception 
	 * 方法功能: 保存订单结算信息
	 */
	public boolean DoSaveEndGather( Takingsorpay to ,String tpflag) throws Exception {
		boolean ins = false;
		CommonDao dbd= new CommonDao();
		if(to!=null){
			//保存或更新 下拉选名称表
			to.setTpflag(tpflag);
			to.setCreatetime(DateUtil.getDateTime());
			if(to.getRealmoney().equals(to.getTpmoney())){
				to.setFinalflag("已结算");
			}else{
//				还剩欠款
				to.setFinalflag("余付款");
			}
			ins = dbd.insertinfor(to);
		}
		return ins;
	}
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @param request
	 * @return 
	 * 方法功能: 根据 sid 查询 对应的订单结算信息
	 */
	public void SelEndGatherInfo(HttpServletRequest request,List list) throws Exception {
		String tpid = CommonDao.isnull(request.getParameter("tpid"));
		CommonDao dbd= new CommonDao();
		Map map = (Map)dbd.queryObjectByPS(FinancialSql.SEL_PAYMONEY_INFO, new SqlParameter(tpid),null);
		request.setAttribute("updinfo", map);
		
		String where = "";
		String orderId = (String) map.get("orderId");
		if(!"".equals(orderId) && orderId.contains(",")){
			String[] orderIds = orderId.split(",");
			where +=" where ";
			for(int i= 0;i<orderIds.length;i++){
				if(!"".equals(orderIds[i])){
					where +=" o.orderId ='"+orderIds[i]+"' or ";
				}
			}
			where = where.substring(0, where.lastIndexOf("or "));
		}
		List orderlist = dbd.DBjsonList(FinancialSql.SEL_ENDGATHER_ORDER + where);
		if(list!=null){
			orderlist.addAll(list);
		}
		request.setAttribute("orderlist",orderlist );
	}
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能: 修改订单结算信息
	 */
	public boolean UpdEndGather( Takingsorpay to ,String tpflag){
		boolean ins = false;
		CommonDao dbo= new CommonDao();
		if(to!=null){
			//保存或更新 下拉选名称表
			to.setTpflag(tpflag);
			to.setCreatetime(DateUtil.getDateTime());
			if(to.getRealmoney().equals(to.getTpmoney())){
				to.setFinalflag("已结算");
			}else{
//				还剩欠款
				to.setFinalflag("余付款");
			}
			ins = dbo.updateinfor(to);
		}
		return ins;
	}
	
	/**
	 * @author 韩飞
	 * @time Nov 25, 2011	
	 * @param request 
	 * @return 
	 * 方法功能: 查询 客户对应当前财务信息
	 */
	public String selClientFinance(HttpServletRequest request ){
		String begin = CommonDao.isnull(request.getParameter("begin")); //开始时间
		String end = CommonDao.isnull(request.getParameter("end")); //结束时间
		request.setAttribute("begin", begin);
		request.setAttribute("end", end);
		
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append(FinancialSql.SEL_CLIENTFINANCE);
		
		String where ="";
		if(!"".equals(begin)&&!"".equals(end)){
			where = " between '"+begin+"' and '"+end+"'";
		
		}else if(!"".equals(begin)){
			where = " between '"+begin+"' and '5000'";
		
		}else if(!"".equals(end)){
			where = " between '0' and '"+end+"'";
		
		}else{
			where = " between '"+DateUtil.getYear()+"-01-01' and '"+DateUtil.getTime()+"'";
		}
		//根据订单时间查询对应金额和数量
		sbSQL.append(" left join (select count(orderid) as ordernum,sum(total) as ordermoney,clientid from orderinfo where 1=1 and ordertime "+where+" group by clientid) as o on o.clientid = c.clientid");
		//根据收款日期查询对应金额
		sbSQL.append(" left join (select sum(realmoney) as realmoney,sum(billmoney) as billmoney,supplierid from takingsorpay where (tpflag = 'order' or tpflag = 'plan') and tpdate "+where+" group by supplierid) as f on f.supplierid = c.clientid");
		
		sbSQL.append(" where 1=1 and (ordermoney!=0 or realmoney!=0)");
		String settle = CommonDao.isnull(request.getParameter("settle")); //结算状态
		request.setAttribute("settle", settle);
		if("done".equals(settle)){
			sbSQL.append(" and ifnull((ordermoney - (ifnull(realmoney,0))) ,0) <= 0 ");
		}else{
			sbSQL.append(" and ifnull((ordermoney - (ifnull(realmoney,0))) ,0) > 0 ");
		}
		sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("clientname") , "=") );
		sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("unitname") , "like") );
		
		return sbSQL.toString();
	}
	
	/**
	 * @author 韩飞
	 * @time Nov 25, 2011	
	 * @param request 
	 * @return 
	 * 方法功能: 查询 客户对应当前财务信息
	 */
	public String selIncomeOrPay(HttpServletRequest request ){
		
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append(FinancialSql.SEL_INCOMEORPAY);
		
		//时间条件查询
		sbSQL.append(new QueryDao().QueryDateInfo(request,"tpdate","begin","end",true));
		
		//sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("clientname") , "=") );
		//sbSQL.append(new QueryDao().QueryInfo(request , new SqlParameter("unitname") , "like") );
		
		return sbSQL.toString();
	}
}
